package com.zengqingfa.examples.mybatisplus.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.WriteWorkbook;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.google.common.collect.Lists;
import com.zengqingfa.examples.mybatisplus.entity.User;
import com.zengqingfa.examples.mybatisplus.excel.dto.*;
import com.zengqingfa.examples.mybatisplus.excel.handler.AddNoHandler;
import com.zengqingfa.examples.mybatisplus.excel.handler.ExcelFillCellMergeStrategy;
import com.zengqingfa.examples.mybatisplus.excel.handler.ExcelWidthStyleStrategy;
import com.zengqingfa.examples.mybatisplus.service.UserService;
import com.zengqingfa.examples.mybatisplus.util.StringUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.beans.BeanUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.Part;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.*;
import java.util.function.Consumer;

/**
 * @author zengqingfa
 * @className EasyExcelController
 * @description
 * @create 2022/12/18 16:45
 */
@RestController
@RequestMapping("/user/easyExcel")
@Slf4j
public class EasyExcelController {


    @Resource
    private UserService userService;

    @PostMapping("/importExcel")
    public void importExcel(@RequestParam("file") MultipartFile file) throws Exception {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader excelReader = EasyExcel.read(file.getInputStream(), excelListener).build();
        // 第一个sheet读取类型
        ReadSheet readSheet1 = EasyExcel.readSheet(0).head(ExportUserModel.class).build();
        // 第二个sheet读取类型
        ReadSheet readSheet2 = EasyExcel.readSheet(1).head(ExportUserModel.class).build();
        // 开始读取第一个sheet
        excelReader.read(readSheet1);
        List<Object> list = excelListener.getDatas();
        list.forEach((user) -> {
            ExportUserModel user1 = (ExportUserModel) user;
            System.out.println(user1.getName() + ", " + user1.getAge() + ", " + user1.getUpdateUserCode());
        });
        // 清空之前的数据
        excelListener.getDatas().clear();
        // 开始读取第二个sheet
        excelReader.read(readSheet2);
        System.out.println("---------------------------------");
        List<Object> list2 = excelListener.getDatas();
        list2.forEach((user) -> {
            ExportUserModel user2 = (ExportUserModel) user;
            System.out.println(user2.getName() + ", " + user2.getAge() + ", " + user2.getUpdateUserCode());
        });
    }


    @PostMapping("/exportExcel")
    public void exportExcel2(HttpServletResponse response, @RequestBody User user) throws Exception {
        String fileName = "报表";
//        response.setContentType("application/vnd.ms-excel");
//        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
        IPage<User> page = userService.selectUserPage1(user);
        List<User> records = page.getRecords();
        List<ExportUserModel> list = Lists.newArrayList();
        records.stream().forEach(r -> {
            ExportUserModel exportUserModel = new ExportUserModel();
            BeanUtils.copyProperties(r, exportUserModel);
            list.add(exportUserModel);
        });
        //这里 需要指定写用哪个class去写
        WriteSheet writeSheet = EasyExcel.writerSheet(0, "学生信息1")
                .head(ExportUserModel.class)
                .relativeHeadRowIndex(3)  //写入头信息开始的行数
                .registerWriteHandler(new MonthSheetWriteHandler())  ////设置拦截器或自定义样式
                .useDefaultStyle(true)
                .build();
        excelWriter.write(list, writeSheet);
        writeSheet = EasyExcel.writerSheet(1, "学生信息2").head(ExportUserModel.class).build();
        excelWriter.write(list, writeSheet);
        //千万别忘记finish 会帮忙关闭流
        excelWriter.finish();
    }


    /**
     * 下载Excel
     *
     * @param response
     */
    @PostMapping(value = "/downloadExcel")
    public void downloadExcel(HttpServletResponse response) throws Exception {
        // 设置响应头
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 设置防止中文名乱码
        String filename = URLEncoder.encode("员工信息", "utf-8");
        // 文件下载方式(附件下载还是在当前浏览器打开)
        response.setHeader("Content-disposition", "attachment;filename=" +
                filename + ".xlsx");
        // 构建写入到excel文件的数据
        List<UserExcel> userExcels = new ArrayList<>();
        UserExcel userExce1 = new UserExcel(1001, "张三", "男", 1333.33, new Date());
        UserExcel userExce2 = new UserExcel(1002, "李四", "男", 1356.83, new Date());
        UserExcel userExce3 = new UserExcel(1003, "王五", "男", 1883.66, new Date());
        UserExcel userExce4 = new UserExcel(1004, "赵六", "男", 1393.39, new Date());
        userExcels.add(userExce1);
        userExcels.add(userExce2);
        userExcels.add(userExce3);
        userExcels.add(userExce4);
        // 写入数据到excel
        EasyExcel.write(response.getOutputStream(), UserExcel.class)
                .sheet("用户信息")
                .doWrite(userExcels);
    }

    @PostMapping("/uploadExcel")
    public String uploadExcel(@RequestParam("file") Part part) throws Exception {
        // 获取上传的文件流
        InputStream inputStream = part.getInputStream();
        // 读取Excel
        EasyExcel.read(inputStream, UserExcel.class,
                new AnalysisEventListener<UserExcel>() {
                    // 每解析一行数据,该方法会被调用一次
                    @Override
                    public void invoke(UserExcel data, AnalysisContext analysisContext) {
                        System.out.println("解析数据为:" + data.toString());
                    }

                    // 全部解析完成被调用
                    @Override
                    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                        System.out.println("解析完成...");
                        // 可以将解析的数据保存到数据库
                    }
                }).sheet().doRead();
        return "上传Excel文件成功";
    }

    @PostMapping("/uploadExcel2")
    public String uploadExcel2(@RequestParam("file") Part part) throws Exception {
        // 获取上传的文件流
        InputStream inputStream = part.getInputStream();
        // 读取Excel
        EasyExcel.read(inputStream, UserExcel.class,
                EasyExcelUtils.getReadListener(dataProcess())).sheet().doRead();
        return "上传Excel文件成功";
    }

    /**
     * 传给监听器的是一个处理解析数据的函数, 当调用consumer的accept方法时就会调用传递的函数逻辑
     * 这里传递的函数是对解析结果集的遍历打印操作, 也可以是数据入库操作
     *
     * @return
     */
    public Consumer<List<UserExcel>> dataProcess() {
        Consumer<List<UserExcel>> consumer = users -> {
            //监听器获取到数据的操作
            System.out.println("=========");
            users.forEach(System.out::println);
        };
        return consumer;
    }


    /**
     * 基于模板导出
     *
     * @param response
     * @throws Exception
     */
    @PostMapping(value = "/templateList")
    public void templateList(HttpServletResponse response) throws Exception {
        // 根据哪个模板进行填充
        String template = "E:\\code\\gitee2\\springboot-examples\\mybatis-demo\\mybatis-plus-starter-demo\\src\\main\\resources\\template\\templateList.xlsx";
        // 设置响应头
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 设置防止中文名乱码
        String filename = URLEncoder.encode("员工信息", "utf-8");
        // 文件下载方式(附件下载还是在当前浏览器打开)
        response.setHeader("Content-disposition", "attachment;filename=" +
                filename + ".xlsx");
        // 构建写入到excel文件的数据
        List<FillData> fillDataList = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            // 构建数据
            FillData fillData = FillData.builder()
                    .userCode("A" + i)
                    .userName("王" + i)
                    .build();
            fillDataList.add(fillData);
        }
        // 写入数据到excel
        EasyExcel.write(response.getOutputStream())
                .withTemplate(template)
                .sheet("用户信息")
                .doFill(fillDataList);
    }

    /**
     * 行头增加序列号
     *
     * @param response response
     */
    @PostMapping("/addSerialNo")
    public void addSerialNo(HttpServletResponse response) throws IOException {
        // 设置响应头
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 设置防止中文名乱码
        String filename = URLEncoder.encode("员工信息", "utf-8");
        // 文件下载方式(附件下载还是在当前浏览器打开)
        response.setHeader("Content-disposition", "attachment;filename=" +
                filename + ".xlsx");
        // 构建写入到excel文件的数据
        List<AddNodeUserDTO> userExcels = new ArrayList<>();
        AddNodeUserDTO userExce1 = new AddNodeUserDTO(1001, "张三", "男", 1333.33, new Date());
        AddNodeUserDTO userExce2 = new AddNodeUserDTO(1002, "李四", "男", 1356.83, new Date());
        AddNodeUserDTO userExce3 = new AddNodeUserDTO(1003, "王五", "男", 1883.66, new Date());
        AddNodeUserDTO userExce4 = new AddNodeUserDTO(1004, "赵六", "男", 1393.39, new Date());
        userExcels.add(userExce1);
        userExcels.add(userExce2);
        userExcels.add(userExce3);
        userExcels.add(userExce4);
        // 写入数据到excel
        EasyExcel.write(response.getOutputStream(), AddNodeUserDTO.class)
                .registerWriteHandler(new AddNoHandler())
                .sheet("用户信息")
                .doWrite(userExcels);
    }

    /**
     * 按规则合并单元格
     *
     * @param response response
     */
    @PostMapping("/mergeCell")
    public void mergeCell(HttpServletResponse response) throws IOException {
        // 设置响应头
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 设置防止中文名乱码
        String filename = URLEncoder.encode("员工信息", "utf-8");
        // 文件下载方式(附件下载还是在当前浏览器打开)
        response.setHeader("Content-disposition", "attachment;filename=" +
                filename + ".xlsx");
        // 构建写入到excel文件的数据
        List<MergeCellUserDTO> userExcels = new ArrayList<>();
        MergeCellUserDTO userExce1 = new MergeCellUserDTO("1001", "张三", "男", 1333.33, new Date());
        MergeCellUserDTO userExce2 = new MergeCellUserDTO("1001", "张三", "男", 1356.83, new Date());
        MergeCellUserDTO userExce3 = new MergeCellUserDTO("1003", "王五", "男", 1883.66, new Date());
        MergeCellUserDTO userExce4 = new MergeCellUserDTO("1004", "赵六", "男", 1393.39, new Date());
        userExcels.add(userExce1);
        userExcels.add(userExce2);
        userExcels.add(userExce3);
        userExcels.add(userExce4);
        //需要合并的列
        int[] mergeColumeIndex = {0, 1, 2};
        // 从那一列开始合并
        int mergeRowIndex = 0;
        // 写入数据到excel
        EasyExcel.write(response.getOutputStream(), MergeCellUserDTO.class)
                .registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex))
                .sheet("用户信息")
                .doWrite(userExcels);
    }


    /**
     * 枚举值转换
     *
     * @param response response
     */
    @PostMapping("/enumConvert")
    public void enumConvert(HttpServletResponse response) throws IOException {
        // 设置响应头
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 设置防止中文名乱码
        String filename = URLEncoder.encode("员工信息", "utf-8");
        // 文件下载方式(附件下载还是在当前浏览器打开)
        response.setHeader("Content-disposition", "attachment;filename=" +
                filename + ".xlsx");
        // 构建写入到excel文件的数据
        List<EnumConvertUserDTO> userExcels = new ArrayList<>();
        EnumConvertUserDTO userExce1 = new EnumConvertUserDTO("1001", "张三", 1, 1333.33, new Date());
        EnumConvertUserDTO userExce2 = new EnumConvertUserDTO("1002", "李四", 1, 1356.83, new Date());
        EnumConvertUserDTO userExce3 = new EnumConvertUserDTO("1003", "王五", 2, 1883.66, new Date());
        EnumConvertUserDTO userExce4 = new EnumConvertUserDTO("1004", "赵六", 2, 1393.39, new Date());
        userExcels.add(userExce1);
        userExcels.add(userExce2);
        userExcels.add(userExce3);
        userExcels.add(userExce4);
        // 写入数据到excel
        EasyExcel.write(response.getOutputStream(), EnumConvertUserDTO.class)
                .sheet("用户信息")
                .doWrite(userExcels);
    }


    /**
     * 针对105W以内的记录数可以调用该方法分多批次查出然后写入到EXCEL的一个SHEET中
     * 注意：
     * 每次查询出来的记录数量不宜过大，根据内存大小设置合理的每次查询记录数，不会内存溢出即可。
     * 数据量不能超过一个SHEET存储的最大数据量105W
     * https://easyexcel.opensource.alibaba.com/docs/2.x/quickstart/write#%E9%87%8D%E5%A4%8D%E5%A4%9A%E6%AC%A1%E5%86%99%E5%85%A5%E5%86%99%E5%88%B0%E5%8D%95%E4%B8%AA%E6%88%96%E8%80%85%E5%A4%9A%E4%B8%AAsheet
     *
     * @throws IOException
     */
    @PostMapping("/exportBigData")
    public void exportBigData(HttpServletResponse response) throws IOException {
        // 设置响应头
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 设置防止中文名乱码
        String filename = URLEncoder.encode("员工信息", "utf-8");
        // 文件下载方式(附件下载还是在当前浏览器打开)
        response.setHeader("Content-disposition", "attachment;filename=" + filename + ".xlsx");
        //模拟从数据库查询数据
        List<ExportBigDataDTO> dataList = Lists.newArrayList();
        for (int i = 0; i < 5000; i++) {
            ExportBigDataDTO dto = new ExportBigDataDTO();
            dto.setId(i);
            dto.setUserCode("userCode-" + i);
            dto.setUserName("userName-" + i);
            dataList.add(dto);
        }
        WriteSheet sheet = new WriteSheet();
        // 设置标题
        List<List<String>> titles = new ArrayList<>();
        titles.add(Arrays.asList("id"));
        titles.add(Arrays.asList("用户编码"));
        titles.add(Arrays.asList("用户名称"));
        sheet.setHead(titles);
        ExcelWriter writer = new ExcelWriter(response.getOutputStream(), ExcelTypeEnum.XLSX);
        // 模拟分批查询：总记录数50条，每次查询20条，  分三次查询 最后一次查询记录数是10
        //数据切割,模拟分页从数据库查询出数据
        List<List<ExportBigDataDTO>> partition = Lists.partition(dataList, 500);
        for (int i = 0; i < partition.size(); i++) {
            List<ExportBigDataDTO> dtos = partition.get(i);
            //控制从第几行开始写入
            sheet.setRelativeHeadRowIndex(500 * i);
            writer.write(dtos, sheet);
        }
//        Integer totalRowCount = dataList.size();
//        Integer pageSize = 100;
//        Integer writeCount = totalRowCount % pageSize == 0 ? (totalRowCount / pageSize) : (totalRowCount / pageSize + 1);
//        // 注： 此处仅仅为了模拟数据，实用环境不需要将最后一次分开，合成一个即可， 参数为：currentPage = i+1;  pageSize = pageSize
//        for (int i = 0; i < writeCount; i++) {
//            // 前两次查询 每次查20条数据
//            if (i < writeCount - 1) {
//                List<List<String>> userList = new ArrayList<>();
//                for (int j = 0; j < pageSize; j++) {
//                    userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
//                }
//                writer.write(userList, sheet);
//            } else if (i == writeCount - 1) {
//                // 最后一次查询 查多余的10条记录
//                List<List<String>> userList = new ArrayList<>();
//                Integer lastWriteRowCount = totalRowCount - (writeCount - 1) * pageSize;
//                for (int j = 0; j < lastWriteRowCount; j++) {
//                    userList.add(Arrays.asList("ID_" + Math.random(), "小明", String.valueOf(Math.random()), new Date().toString()));
//                }
//                writer.write(userList, sheet);
//            }
//        }
        writer.finish();
    }

    /**
     * 动态列：标题动态
     *
     * @param response response
     */
    @PostMapping("/dynamicColumn")
    public void dynamicColumn(HttpServletResponse response) throws Exception {
        // 设置响应头
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 设置防止中文名乱码
        String filename = URLEncoder.encode("员工信息", "utf-8");
        // 文件下载方式(附件下载还是在当前浏览器打开)
        response.setHeader("Content-disposition", "attachment;filename=" + filename + ".xlsx");
        //模拟从数据库查询数据
        List<DynamicColumnDTO> dataList = Lists.newArrayList();
        for (int i = 0; i < 20; i++) {
            DynamicColumnDTO dto = new DynamicColumnDTO();
            dto.setId(i);
            dto.setUserCode("dynamic-code-" + i);
            dto.setUserName("dynamic-name-" + i);
            dataList.add(dto);
        }
        //list转换为需要的字段: userCode userName
        Set<String> set = new HashSet<>();
        set.add("userCode");
        set.add("id");
        List<List<Object>> list = Lists.newArrayList();
        for (int i = 0; i < dataList.size(); i++) {
            DynamicColumnDTO dynamicColumnDTO = dataList.get(i);
            Field[] declaredFields = dynamicColumnDTO.getClass().getDeclaredFields();
            List<Object> values=Lists.newArrayList();
            for (int j = 0; j < declaredFields.length; j++) {
                Field field = declaredFields[j];
                field.setAccessible(true);
                if (set.contains(field.getName())) {
                    String itemName = StringUtils.capitalizeFirstLetter(field.getName());
                    Method getMethod = DynamicColumnDTO.class.getMethod("get" + itemName);
                    Object value = getMethod.invoke(dynamicColumnDTO);
                    values.add(value);
                }
            }
            list.add(values);
        }
        // 设置标题
        List<List<String>> titles = new ArrayList<>();
        titles.add(Arrays.asList("id"));
        titles.add(Arrays.asList("用户编码"));
//        titles.add(Arrays.asList("用户名称"));
        // 写入数据到excel
        EasyExcel.write(response.getOutputStream(), DynamicColumnDTO.class)
                .sheet("用户信息")
                .registerWriteHandler(new ExcelWidthStyleStrategy()) // 设置宽度
                .head(titles)
                .doWrite(list);
    }


}